package com.yaozheng.vocationaltraining.db;

import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.yaozheng.vocationaltraining.activity.BaseActivity;
import com.yaozheng.vocationaltraining.utils.JsonUtils;
import com.yaozheng.vocationaltraining.utils.TypeUtils;

public class CoursewareCacheDao {
	/**
	 * 表名字
	 */
	public static final String TABLE_NAME = "courseware_db_cache";

	/**
	 * 模块内容
	 */
	public static final String ID = "id";
	/**
	 * 内容
	 */
	public static final String CONTENT = "content";

	/**
	 * 添加时间
	 */
	public static final String ADDTIME = "addtime";
	/**
	 * 状态
	 */
	public static final String STATUS = "status";
	/**
	 * 创建表sql
	 */
	public static final String CREATE_TABLE_SQL = "create table if not exists "
			+ TABLE_NAME + " (" + ID + " integer  PRIMARY KEY, " + CONTENT
			+ " TEXT," + ADDTIME
			+ " datetime DEFAULT (datetime('now', 'localtime'))," + STATUS
			+ " integer );";
	protected DbOpenHelper dbHelper;

	public CoursewareCacheDao(BaseActivity baseActivity) {
		dbHelper = DbOpenHelper.getInstance(baseActivity);
	}

	protected CoursewareCacheDao() {
		super();
	}

	/**
	 * 保存
	 * 
	 * @param coursewareJsonObject
	 * @throws Exception
	 */
	public synchronized void save(JSONObject coursewareJsonObject)
			throws Exception {
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db.isOpen()) {
				ContentValues values = new ContentValues();
				values.put(ID,
						TypeUtils.getJsonInteger(coursewareJsonObject, "id"));
				values.put(CONTENT, coursewareJsonObject.toString());
				values.put(STATUS, 0);
				db.replace(TABLE_NAME, null, values);
			}
		} finally {
			if (db != null && db.isOpen()) {
				db.close();
				db = null;

			}
		}
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @throws Exception
	 */
	public synchronized void delete(String id) throws Exception {
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db.isOpen()) {
				db.delete(TABLE_NAME, ID + " = ?", new String[] { id });
			}
		} finally {
			if (db != null && db.isOpen()) {
				db.close();
				db = null;

			}
		}
	}

	/**
	 * 得到一项
	 * 
	 * @param id
	 * @return
	 */
	public synchronized JSONObject get(String id) {
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = dbHelper.getReadableDatabase();
			if (db.isOpen()) {
				String sql = "select " + CONTENT + " from " + TABLE_NAME
						+ " where " + STATUS + "=0 and " + ID + "=" + id;
				cursor = db.rawQuery(sql, null);
				if (cursor.moveToNext()) {
					return JsonUtils.getJsonObject(cursor.getString(cursor
							.getColumnIndex(CONTENT)));
				}
			}
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
			if (db != null && db.isOpen()) {
				db.close();
				db = null;

			}
		}
		return null;
	}

	/**
	 * 得到列表
	 * 
	 * @return
	 */
	public synchronized JSONArray getList() {
		SQLiteDatabase db = null;
		Cursor cursor = null;
		JSONArray listData = new JSONArray();
		try {
			db = dbHelper.getReadableDatabase();
			if (db.isOpen()) {
				String sql = "select " + CONTENT + " from " + TABLE_NAME
						+ " where " + STATUS + "=0 ";
				cursor = db.rawQuery(sql, null);
				while (cursor.moveToNext()) {
					listData.add(JsonUtils.getJsonObject(cursor
							.getString(cursor.getColumnIndex(CONTENT))));
				}
			}
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;
			}
			if (db != null && db.isOpen()) {
				db.close();
				db = null;

			}
		}
		return listData;
	}
}
